<?php 
    class DormitoryManagementDAL{
        //
        public function removeDormitoryManagementJurisdiction($removeList){
            $result=false;
            $query="update tb_instructor_dormitory_allocating_list set status=0 
            where uid=:uid and grade=:grade and major_id=:major_id and status=1;";

            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();
                
                $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                foreach($removeList as $remove){
                    $statement->bindValue(":uid",$remove->uid);
                    $statement->bindValue(":grade",$remove->grade);
                    $statement->bindValue(":major_id",$remove->major);
                    $result=$statement->execute();
                }
                
                $pdo->commit();
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }

            return $result;
        }

        //添加教师所管理的宿舍,未使用
        public function addInstructorDormitory(){
            $result=false;
            $query="insert into tb_instructor_dormitory_allocating_list(uid,grade,major_id,apartment_id,floor,dormitory_id,add_time,status) 
            values(:uid,:grade,:major_id,:apartment_id,:floor,:dormitory_id,now(),:status);";
            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();
                
                $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":uid",$instructorInfo->uid);
                $statement->bindValue(":grade",$instructorInfo->grade);
                $statement->bindValue(":major_id",$instructorInfo->major);
                $statement->bindValue(":apartment_id",$instructorInfo->apartment_id);
                $statement->bindValue(":floor",$instructorInfo->floor);
                $statement->bindValue(":dormitory_id",$instructorInfo->dormitory_id);
                $statement->bindValue(":status",1);
                $result=$statement->execute();

                $pdo->commit();
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }

            return $result;
        }
        //删除宿舍管理权限,未使用
        public function deleteInstructorDormitory($instructorInfo,$dormitoryList){
            $result=false;
            //$query="delete from tb_instructor_dormitory_allocating_list where";
            $query="update tb_instructor_dormitory_allocating_list set status=0 
            where $uid=:uid and grade=:grade and major_id=major_id and status=1;";

            $query_dormitory="update tb_dormitory_info set status=1 
            where apartmnet_id=:apartment_id and floor=:floor and dormitory_id=:dormitory_id status=2;";

            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();
                
                $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":uid",$instructorInfo->uid);
                $statement->bindValue(":grade",$instructorInfo->grade);
                $statement->bindValue(":major_id",$instructorInfo->major);
                $statement->execute();

                $statement=$pdo->prepare($query_dormitory,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                foreach($dormitoryList as $dormitory){
                    $statement->bindValue(":apartment_id",$instructorInfo->uid);
                    $statement->bindValue(":floor",$instructorInfo->grade);
                    $statement->bindValue(":dormitory_id",$instructorInfo->major);
                    $result=$statement->execute();
                }
                if($result){
                    $pdo->commit();
                }
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }

            return $result;
        }

        //获取辅导员所管理的宿舍的数量
        public function getInstructorDormitoryNumber($pattern,$instructorID,$grade,$major){
            //1
            $query="select count(1) as count,count(beds) as beds from tb_instructor_dormitory_allocating_list 
            where uid=:uid and status=1;";

            //2
            $query_major="select count(1) as count,count(beds) as beds from tb_instructor_dormitory_allocating_list 
            where status=1 and uid=:uid and major_id=:major_id;";

            //3
            $query_grade="select count(1) as count,count(beds) as beds from tb_instructor_dormitory_allocating_list 
            where uid=:uid and status=1 and grade=:grade;";

            //4
            $query_grade_major="select count(1) as count,count(beds) as beds from tb_instructor_dormitory_allocating_list 
            where uid=:uid and status=1 and grade=:grade and major_id=:major_id;";

            $pdo=myPDO::getInstance();
            switch($pattern){
                case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":uid",$instructorID);
                    break;
                case 2:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":uid",$instructorID);
                    $statement->bindValue(":major_id",$major);
                    break;
                case 3:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":uid",$instructorID);
                    $statement->bindValue(":grade",$grade);
                    break;
                case 4:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":uid",$instructorID);
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    break;
                default:break;
            }
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }

        //获取已分配宿舍的辅导员
        public function getInstructorList($action,$grade,$major,$page=0,$pageSize=10,$search){
            //1查询全部
            $query="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            order by add_time desc LIMIT :page,:pageSize;";

            //2查询全部，带查询条件，以账号或姓名查询
            $query_search="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            where (uid=:search_uid or name=:search_name) 
            order by add_time desc LIMIT :page,:pageSize;";

            //3按专业查询
            $query_major="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            where major_id=:major_id 
            order by add_time desc LIMIT :page,:pageSize;";

            //4按专业查询，带查询条件
            $query_major_search="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_managementyes 
            where major_id=:major_id 
            and (uid=:search_uid or name=:search_name) 
            order by add_time desc LIMIT :page,:pageSize;";

            //5按年级查询
            $query_grade="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            order by add_time desc LIMIT :page,:pageSize;";

            //6按年级查询，带查询条件
            $query_grade_search="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            and (uid=:search_uid or name=:search_name) 
            order by add_time desc LIMIT :page,:pageSize;";

            //7按年级与专业查询
            $query_grade_major="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            and major_id=:major_id 
            order by add_time desc LIMIT :page,:pageSize;";

            //8按年级与专业查询，带查询条件
            $query_grade_major_search="select uid,name,phone,grade,major_name,major_id,add_time 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            and major_id=:major_id 
            and (uid=:search_uid or name=:search_name) 
            order by add_time desc LIMIT :page,:pageSize;";

            $pdo=myPDO::getInstance();
            switch($action){
                case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 2:
                    $statement=$pdo->prepare($query_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 3:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 4:
                    $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 5:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 6:
                    $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 7:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 8:
                    $statement=$pdo->prepare($query_grade_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                default:break;
            }

            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }

        //获取页数
        public function getInstructorListPage($action,$grade,$major,$search){
            //1查询全部
            $query="select count(1) as count 
            from view_instructor_dormitory_management_yes;";

            //2查询全部，带查询条件，以账号或姓名查询
            $query_search="select count(1) as count 
            from view_instructor_dormitory_management_yes 
            where (uid=:search or name=:search);";

            //3按专业查询
            $query_major="select count(1) as count 
            from view_instructor_dormitory_management_yes 
            where major_id=:major_id;";

            //4按专业查询，带查询条件
            $query_major_search="select count(1) as count 
            from view_instructor_dormitory_managementyes 
            where major_id=:major_id 
            and (uid=:search or name=:search);";

            //5按年级查询
            $query_grade="select count(1) as count 
            from view_instructor_dormitory_management_yes 
            where grade=:grade;";

            //6按年级查询，带查询条件
            $query_grade_search="select count(1) as count 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            and (uid=:search or name=:search);";

            //7按年级与专业查询
            $query_grade_major="select count(1) as count 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            and major_id=:major_id;";

            //8按年级与专业查询，带查询条件
            $query_grade_major_search="select count(1) as count 
            from view_instructor_dormitory_management_yes 
            where grade=:grade 
            and major_id=:major_id 
            and (uid=:search_uid or name=:search_name);";

            $pdo=myPDO::getInstance();
            switch($action){
                case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    break;
                case 2:
                    $statement=$pdo->prepare($query_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                case 3:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    break;
                case 4:
                    $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                case 5:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    break;
                case 6:
                    $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                case 7:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    break;
                case 8:
                    $statement=$pdo->prepare($query_grade_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                default:break;
            }

            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }

        //获取未分配宿舍的辅导员
        public function getInstructorNoList($action,$grade,$major,$page=0,$pageSize=10,$search){
            //所有查询只查询未分配宿舍的辅导员

            //1 查询全部
            $query="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            order by add_time LIMIT :page,:pageSize;";

            //2 查询全部，带查询条件，以账号或姓名查询
            $query_search="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where (uid=:search or name=:search) 
            order by add_time LIMIT :page,:pageSize;";

            //3 按专业查询，不带查询条件
            $query_major="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where major_id=:major_id  
            order by add_time LIMIT :page,:pageSize;";

            //4 按专业查询，带查询条件,以账号或姓名查询
            $query_major_search="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where major_id=:major_id 
            and (uid=:search or name=:search) 
            order by add_time LIMIT :page,:pageSize;";

            //5 按年级查询，不带查询条件
            $query_grade="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            order by add_time LIMIT :page,:pageSize;";

            //6 按年级查询，带查询条件,以账号或姓名查询
            $query_grade_search="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            and (uid=:search or name=:search) 
            order by add_time LIMIT :page,:pageSize;";

            //7 按年级与专业查询，不带查询条件
            $query_grade_major="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            and major_id=:major_id 
            order by add_time LIMIT :page,:pageSize;";

            //8 按年级与专业查询，带查询条件，以账号或姓名查询
            $query_grade_major_search="select uid,name,phone,grade,major_id,major_name,add_time 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            and major_id=:major_id 
            and (uid=:search or name=:search) 
            order by add_time LIMIT :page,:pageSize;";

            $pdo=myPDO::getInstance();
            switch($action){
                case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 2:
                    $statement=$pdo->prepare($query_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 3:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 4:
                    $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 5:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 6:
                    $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 7:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                case 8:
                    $statement=$pdo->prepare($query_grade_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                default:break;
            }

            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }

        //获取页数
        public function getInstructorNoListPage($action,$grade,$major,$search){
            //所有查询只查询未分配宿舍的辅导员
            //1 查询全部
            $query="select count(1) as count 
            from view_instructor_dormitory_management_no;";

            //2 查询全部，带查询条件，以账号或姓名查询
            $query_search="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where (uid=:search or name=:search);";

            //3 按专业查询，不带查询条件
            $query_major="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where major_id=:major_id;";

            //4 按专业查询，带查询条件,以账号或姓名查询
            $query_major_search="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where major_id=:major_id 
            and (uid=:search or name=:search);";

            //5 按年级查询，不带查询条件
            $query_grade="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where grade=:grade;";

            //6 按年级查询，带查询条件,以账号或姓名查询
            $query_grade_search="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            and (uid=:search or name=:search);";

            //7 按年级与专业查询，不带查询条件
            $query_grade_major="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            and major_id=:major_id;";

            //8 按年级与专业查询，带查询条件，以账号或姓名查询
            $query_grade_major_search="select count(1) as count 
            from view_instructor_dormitory_management_no 
            where grade=:grade 
            and major_id=:major_id 
            and (uid=:search or name=:search);";

            $pdo=myPDO::getInstance();
            switch($action){
                case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    break;
                case 2:
                    $statement=$pdo->prepare($query_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                case 3:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    break;
                case 4:
                    $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                case 5:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    break;
                case 6:
                    $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                case 7:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    break;
                case 8:
                    $statement=$pdo->prepare($query_grade_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                default:break;
            }

            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }
        //获取辅导员列表
        public function getInstructorAllList($action,$grade,$major,$page=0,$pageSize=10,$search){
            $result=null;
            //1 查询全部
            $query='select uid,name,phone,grade,major_id,major_name,add_time 
             from view_instructor_dormitory_management_all 
             order by add_time LIMIT :page,:pageSize;';

             //2 查询全部，带查询条件，以账号或名字查询
             $query_search="select uid,name,phone,grade,major_id,major_name,add_time 
             from view_instructor_dormitory_management_all 
             where (uid=:search_uid or name=:search_name) 
             order by add_time LIMIT :page,:pageSize;";

             //3 按专业查询所有
             $query_major='select uid,name,phone,grade,major_id,major_name,add_time   
             from view_instructor_dormitory_management_all 
             where major_id=:major_id 
             order by add_time LIMIT :page,:pageSize;';

             //4 按专业查询,带查询条件，以账号或名字查询
             $query_major_search='select uid,name,phone,grade,major_id,major_name,add_time  
             from view_instructor_dormitory_management_all 
             where major_id=:major_id 
             and (uid=:search_uid or name=:search_name) 
             order by add_time LIMIT :page,:pageSize;';

             //5 按年级查询所有
             $query_grade='select uid,name,phone,grade,major_id,major_name,add_time  
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             order by add_time LIMIT :page,:pageSize;';

             //6 按年级查询,带查询条件，以账号或名字查询
             $query_grade_search='select uid,name,phone,grade,major_id,major_name,add_time   
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             and (uid=:search_uid or name=:search_name) 
             order by add_time LIMIT :page,:pageSize;';

             //7 按年级、专业查询
             $query_grade_major='select uid,name,phone,grade,major_id,major_name,add_time  
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             and major_id=:major_id 
             order by add_time LIMIT :page,:pageSize;';

             //8 按年级、专业查询,带查询条件，以账号或名字查询
             $query_grade_major_search='select uid,name,phone,grade,major_id,major_name,add_time 
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             and major_id=:major_id 
             and (uid=:search_uid or name=:search_name) 
             order by add_time LIMIT :page,:pageSize;';

             $pdo=myPDO::getInstance();
             switch($action){
                 case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                 case 2:
                    $statement=$pdo->prepare($query_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                 case 3:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                 break;
                 case 4:
                    $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                 case 5:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                 break;
                 case 6:
                    $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                 case 7:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                 case 8:
                    $statement=$pdo->prepare($query_grade_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    $statement->bindValue(":page",$page,PDO::PARAM_INT);
                    $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
                    break;
                 default:break;
             }
             if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }

        //获取页数
        //获取辅导员列表
        public function getInstructorAllListPage($action,$grade,$major,$search){
            $result=null;
            //1 查询全部
            $query='select count(1) as count 
             from view_instructor_dormitory_management_all;';

             //2 查询全部，带查询条件，以账号或名字查询
             $query_search="select count(1) as count 
             from view_instructor_dormitory_management_all 
             where (uid=:search_uid or name=:search_name);";

             //3 按专业查询所有
             $query_major='select count(1) as count  
             from view_instructor_dormitory_management_all 
             where major_id=:major_id;';

             //4 按专业查询,带查询条件，以账号或名字查询
             $query_major_search='select count(1) as count 
             from view_instructor_dormitory_management_all 
             where major_id=:major_id 
             and (uid=:search_uid or name=:search_name);';

             //5 按年级查询所有
             $query_grade='select count(1) as count 
             from view_instructor_dormitory_management_all 
             where grade=:grade;';

             //6 按年级查询,带查询条件，以账号或名字查询
             $query_grade_search='select count(1) as count 
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             and (uid=:search_uid or name=:search_name);';

             //7 按年级、专业查询
             $query_grade_major='select count(1) as count 
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             and major_id=:major_id;';

             //8 按年级、专业查询,带查询条件，以账号或名字查询
             $query_grade_major_search='select count(1) as count 
             from view_instructor_dormitory_management_all 
             where grade=:grade 
             and major_id=:major_id 
             and (uid=:search_uid or name=:search_name);';

             $pdo=myPDO::getInstance();
             switch($action){
                 case 1:
                    $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    break;
                 case 2:
                    $statement=$pdo->prepare($query_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                 case 3:
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                 break;
                 case 4:
                    $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                 case 5:
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                 break;
                 case 6:
                    $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                 case 7:
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    break;
                 case 8:
                    $statement=$pdo->prepare($query_grade_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(":grade",$grade);
                    $statement->bindValue(":major_id",$major);
                    $statement->bindValue(":search_uid",$search);
                    $statement->bindValue(":search_name",$search);
                    break;
                 default:break;
             }
             if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
             }
             $statement->closeCursor();
             return $result;
        }

        public function getInstructorDormitoryList(){

        }

        
    }
?>